package com.hw.utils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;

import com.hw.dao.utils.OraclePageDaoBase;

//构造按时间分区查询sql语句及参数--分区按时间分区
public class PartitionQueryUtil {
	
	private OraclePageDaoBase oraclePageDaoBase;
	
	private String sql;  //sql语句
	private String tableName ;  //分区表名
	List<Object> args = new ArrayList<Object>();//参数
	private Date startTime ;  //开始时间
	private Date endTime ;  //结束时间
	List<Object> resultArgs = new ArrayList<Object>();//结果参数

	//结果sql
	public String getResultSql(){
		String par = "";//时间Str
		StringBuffer varSql = new StringBuffer("");
		if(StringUtils.isNotEmpty(tableName.trim())&&StringUtils.isNotEmpty(sql.trim())){
			List<String> parlist = getPartition();//获取表分区
			int parNum = parlist.size();
			if(parNum>0){
				for(int i=0;i<parNum;i++){
					par = parlist.get(i);
					if(i==0){
						varSql.append(sql.replaceAll(tableName, tableName+" partition("+par+")"));
					}else{
						varSql.append("\n UNION ALL \n"+sql.replaceAll(tableName, tableName+" partition("+par+")"));
					}
					resultArgs.addAll(args);
				}
			}else{
				System.out.println("------表分区不存在或时间格式不正确!!!");
			}
		}else{
			System.out.println("------sql或分区表名不能为空!!!");
		}
		return varSql.toString();
	}
	//结果参数
	public List<Object> getResultArgs(){
		return resultArgs;
	}
	
	public String getSql() {
		return sql;
	}
	public void setSql(String sql) {
		this.sql = sql;
	}
	public List<Object> getArgs() {
		return args;
	}
	public void setArgs(List<Object> args) {
		this.args = args;
	}
	public Date getStartTime() {
		return startTime;
	}
	public void setStartTime(Date startTime) {
		this.startTime = startTime;
	}
	public Date getEndTime() {
		return endTime;
	}
	public void setEndTime(Date endTime) {
		this.endTime = endTime;
	}
	public String getTableName() {
		return tableName;
	}
	public void setTableName(String tableName) {
		this.tableName = tableName;
	}
	public PartitionQueryUtil(){
		super();
	}
	public PartitionQueryUtil(String sql, String tableName, List<Object> args,Date startTime, Date endTime,OraclePageDaoBase oraclePageDaoBase) {
		super();
		this.sql = sql;
		this.tableName=tableName;
		this.args = args;
		this.startTime = startTime;
		this.endTime = endTime;
		this.oraclePageDaoBase = oraclePageDaoBase;
	} 
	
	//获取表分区
	public List<String> getPartition(){
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");//按周
		Calendar cal = Calendar.getInstance();
		cal.setTime(startTime);
		int w = cal.get(Calendar.DAY_OF_WEEK)-1;
		int add=0;
		if(w<5){
			add =-(w+7-5);
		}else{
			add = -(w-5);
		}
		cal.add(Calendar.DATE, add);
		String st = sdf.format(cal.getTime());
		
		Calendar cal2 = Calendar.getInstance();
		cal2.setTime(endTime);
		int w2 = cal2.get(Calendar.DAY_OF_WEEK)-1;
		int add2=0;
		if(w2<=5){
			add2 = 5-w2;
		}else{
			add2 = 6;
		}
		cal2.add(Calendar.DATE, add2);
		String et = sdf.format(cal2.getTime());
		
		String sql_par = " select partition_name from user_tab_partitions where table_name = '"+tableName+"' "
				+ " and substr(partition_name,-8)>='"+st+"' and substr(partition_name,-8)<='"+et+"' order by table_name desc ";
		List<String> par = oraclePageDaoBase.query(sql_par, new RowMapper<String>(){
			@Override
			public String mapRow(ResultSet rs, int arg1)throws SQLException {
				return rs.getString("partition_name");
			}
		});
		return par;
	}
}
